/**
 *
 * @author cuntmaster
 */
package erp.projekt;

import java.sql.*;
import java.util.*;

public class SqlProduktLieferanten {

    private static final String CONN_USER = "erpworker";
    private static final String CONN_PASSWORD = "erpworker";
    private static final String CONN_HOST = "10.4.101.125";
    private static final int CONN_PORT = 3306;
    private static final String CONN_DB = "erp";
    private static Connection conn = null;
    private static Statement stmnt = null;
    ResultSet result;
    //String[] produktGruppen;
    List<String> produktLieferanten = new ArrayList<>();

    public SqlProduktLieferanten() throws SQLException {
        this.connectDB();
        produktLieferanten = this.getProduktLieferanten();
    }

    private void connectDB() {
        try {
            // Datenbanktreiber für ODBC Schnittstellen laden.
            Class.forName("com.mysql.jdbc.Driver");
            // Verbindung zur ODBC-Datenbank
            conn = DriverManager.getConnection("jdbc:mysql://" + CONN_HOST + ":"
                    + CONN_PORT + "/" + CONN_DB + "?" + "user=" + CONN_USER + "&"
                    + "password=" + CONN_PASSWORD);

            System.out.println("success");
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    private ArrayList<String> getProduktLieferanten() throws SQLException {

        if (conn != null) {
            try {
                String query = "SELECT ProduktLieferantFirma1 FROM erp.tblproduktlieferant";

                stmnt = conn.createStatement();

                result = stmnt.executeQuery(query);

                while (result.next()) {
                    produktLieferanten.add(result.getString("ProduktLieferantFirma1"));
                }

            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return (ArrayList<String>) produktLieferanten;
    }

    public String getProduktLieferantById(int ID) throws SQLException {
        String produktLieferant = "";

        if (conn != null) {
            try {
                String query = "SELECT ProduktLieferantFirma1 FROM tblproduktlieferant WHERE ProduktLieferantId=" + ID;
                stmnt = conn.createStatement();
                result = stmnt.executeQuery(query);

                while (result.next()) {
                    produktLieferant = result.getString("ProduktLieferantFirma1");
                }
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
        return produktLieferant;
    }
    
    public void neuerProduktLieferant(String firma, String strasse, String land, String plz, String ort) {
        if (conn != null) {
            try {
                String query = "INSERT INTO tblproduktlieferant (ProduktLieferantFirma, ProduktLieferantStrasse, "
                        + "ProduktLieferantLand, ProduktLieferantPLZ, ProduktLieferantOrt ) VALUES ('" + firma + "','" + strasse + "','" + land +
                        "'plz'," + "'ort'" + ")";
                stmnt = conn.createStatement();
                stmnt.executeUpdate(query);
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
}
